import math
import re

import xlrd
from model.UniversityPlanScore import UniversityPlanScore
from model.UniversityScoreRank import UniversityScoreRank
from model.UniversityMajorRank import UniversityMajorRank
from model.UniversityPlanScoreError import UniversityPlanScoreError
from model.University import University
from model.OneScoreRank import OneScoreRank
import sys


# 导入《民族班》数据 理科
def import_score_1():
    data = xlrd.open_workbook('../static/excel_2023/minzu.xls')
    table = data.sheet_by_name('Sheet1')
    rowNum = table.nrows
    colNum = table.ncols
    print(rowNum, colNum)
    universityPlanScore = UniversityPlanScore()
    university = University()
    oneScoreRank = OneScoreRank()
    universityScoreRank = UniversityScoreRank()
    universityMajorRank = UniversityMajorRank()
    for i in range(rowNum):
        print(i)
        row = dict()
        university_row = dict()
        for j in range(colNum):
            row['batch'] = 9
            if j == 0:
                row['year'] = 2022
            if j == 1:
                row['code'] = int(table.cell(i, j).value)
                university_row = university.getOneByCode(row['code'])
            if j == 2:
                row['name'] = table.cell(i, j).value
            if university_row:
                row['f211'] = university_row['f211']
                row['f985'] = university_row['f985']
                row['dual_class_name'] = university_row['dual_class_name']
                row['nature_name'] = university_row['nature_name']
                row['province_code'] = university_row['province_code']
                row['city_code'] = university_row['city_code']
                row['province'] = university_row['province']
                row['city'] = university_row['city']
            else:
                row['f211'] = 0
                row['f985'] = 0
                row['dual_class_name'] = 0
                row['nature_name'] = 0
                row['province_code'] = 0
                row['city_code'] = 0
                row['province'] = ''
                row['city'] = ''

            if j == 3:
                if table.cell(i, j).value:
                    row_1 = row
                    row_1['type'] = 1
                    row_1['lowest_score'] = int(table.cell(i, j).value)

                    # 计算最低分，最低排名
                    lowest_rank = oneScoreRank.getOneByScore(row['lowest_score'], row['year'], row['type'])
                    if lowest_rank:
                        row_1['lowest_rank'] = lowest_rank['total_num']
                    else:
                        row_1['lowest_rank'] = 0

                    row_1['highest_score'] = 0
                    row_1['highest_rank'] = 0
                    row_1['mean_score'] = 0
                    row_1['mean_rank'] = 0
                    row_1['plan_number'] = 0
                    row_1['submit_number'] = 0
                    row_1['enter_number'] = 0
                    row_1['score'] = ''
                    exit_row = universityScoreRank.getOneByYearBatch(row_1['year'], row_1['batch'], row_1['type'], row_1['code'])
                    if exit_row:
                        print('存在，不处理', row_1)
                        pass
                    else:
                        print('新增', row_1)
                        universityScoreRank.toInsert(row_1)


# 导入《民族班》数据 文科
def import_score_2():
    data = xlrd.open_workbook('../static/excel_2023/minzu.xls')
    table = data.sheet_by_name('Sheet1')
    rowNum = table.nrows
    colNum = table.ncols
    print(rowNum, colNum)
    universityPlanScore = UniversityPlanScore()
    university = University()
    oneScoreRank = OneScoreRank()
    universityScoreRank = UniversityScoreRank()
    universityMajorRank = UniversityMajorRank()
    for i in range(rowNum):
        print(i)
        row = dict()
        university_row = dict()
        for j in range(colNum):
            row['batch'] = 9
            if j == 0:
                row['year'] = 2022
            if j == 1:
                row['code'] = int(table.cell(i, j).value)
                university_row = university.getOneByCode(row['code'])
            if j == 2:
                row['name'] = table.cell(i, j).value
            if university_row:
                row['f211'] = university_row['f211']
                row['f985'] = university_row['f985']
                row['dual_class_name'] = university_row['dual_class_name']
                row['nature_name'] = university_row['nature_name']
                row['province_code'] = university_row['province_code']
                row['city_code'] = university_row['city_code']
                row['province'] = university_row['province']
                row['city'] = university_row['city']
            else:
                row['f211'] = 0
                row['f985'] = 0
                row['dual_class_name'] = 0
                row['nature_name'] = 0
                row['province_code'] = 0
                row['city_code'] = 0
                row['province'] = ''
                row['city'] = ''

            if j == 4:
                if table.cell(i, j).value:
                    row_2 = row
                    row_2['type'] = 2
                    row_2['lowest_score'] = int(table.cell(i, j).value)

                    # 计算最低分，最低排名
                    lowest_rank = oneScoreRank.getOneByScore(row['lowest_score'], row['year'], row['type'])
                    if lowest_rank:
                        row_2['lowest_rank'] = lowest_rank['total_num']
                    else:
                        row_2['lowest_rank'] = 0
                    row_2['highest_score'] = 0
                    row_2['highest_rank'] = 0
                    row_2['mean_score'] = 0
                    row_2['mean_rank'] = 0
                    row_2['plan_number'] = 0
                    row_2['submit_number'] = 0
                    row_2['enter_number'] = 0
                    row_2['score'] = ''
                    print(row_2)
                    exit_row = universityScoreRank.getOneByYearBatch(row_2['year'], row_2['batch'], row_2['type'], row_2['code'])
                    if exit_row:
                        pass
                    else:
                        universityScoreRank.toInsert(row_2)


if __name__ == "__main__":
    import_score_2()